package com.c2soft.nepalcmx.dao;

import java.util.Iterator;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;





import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.c2soft.nepalcmx.command.NetPositionCommand;


public class ProfitLossDAO {

	@Autowired
	private DataSource dataSource;

	private JdbcTemplate jdbcTemplate;
	
	public List<NetPositionCommand> findProfitLoss(String userId) {
		String sql = "SELECT SUM(ql),SUM(price),symbol,user_Id,ptype FROM ordermgmt where user_id=? "
				+ "and type='SELL' group by symbol,ptype,price ";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<NetPositionCommand> profitloss=jdbcTemplate.query(sql,new Object[] {userId},new ProfitLossMapper());
		
		return profitloss;
	}
	
	private final class ProfitLossMapper implements RowMapper<NetPositionCommand> {

	    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
	    	NetPositionCommand netpos = new NetPositionCommand();
	    	netpos.setLot1(rs.getString(1));
	    	netpos.setPrice1(rs.getString(2));
	    	netpos.setSymbol(rs.getString(3));
	    	netpos.setUserId(rs.getString(4));
	    	netpos.setPtype(rs.getString(5));
	    	String sql1 ="SELECT price,symbol FROM ordermgmt  where type='BUY' and user_Id='"+netpos.getUserId()+
	    			"' and ptype='"+netpos.getPtype()+"' and symbol='"+netpos.getSymbol()+"' group by price";
	    	List netPositions=jdbcTemplate.query(sql1,new ProfitLossMapper1());
	    	Iterator itr=netPositions.iterator();
	    	while(itr.hasNext()){
	    		NetPositionCommand npc=(NetPositionCommand)itr.next();
	    		if(npc.getSymbol().equals(netpos.getSymbol())){
	    		   		netpos.setPrice2(npc.getPrice2());
	    		}
	    	}
	        return netpos;
	    } 
	}
	
	 private final class ProfitLossMapper1 implements RowMapper<NetPositionCommand> {

		    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
		    	NetPositionCommand netpos = new NetPositionCommand();
		     	netpos.setPrice2(rs.getString(1));
		      	netpos.setSymbol(rs.getString(2));
		        return netpos;
		    } 
	    }
	 
	 /*************************************************************/
	 public List<NetPositionCommand> findBrokerProfitLoss(String brokerId) {
			String sql = "SELECT SUM(ql),price,symbol,brokeId,ptype FROM ordermgmt where brokeId=? and user_id='' "
					+ "and type='SELL' group by symbol,ptype,price ";
			jdbcTemplate = new JdbcTemplate(dataSource);
			List<NetPositionCommand> profitloss=jdbcTemplate.query(sql,new Object[] {brokerId},new BrokerProfitLossMapper());
			return profitloss;
		}
		private final class BrokerProfitLossMapper implements RowMapper<NetPositionCommand> {

		    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
		    	NetPositionCommand netpos = new NetPositionCommand();
		    	netpos.setLot1(rs.getString(1));
		    	netpos.setPrice1(rs.getString(2));
		    	netpos.setSymbol(rs.getString(3));
		    	netpos.setBrokeId(rs.getString(4));
		    	netpos.setPtype(rs.getString(5));
		    	String sql1 ="SELECT price,symbol FROM ordermgmt  where type='BUY' and brokeId='"+netpos.getBrokeId()+
		    			"' and user_id='' and ptype='"+netpos.getPtype()+"' and symbol='"+netpos.getSymbol()+"' group by price";
		    	List netPositions=jdbcTemplate.query(sql1,new BrokerProfitLossMapper1());
		    	Iterator itr=netPositions.iterator();
		    	while(itr.hasNext()){
		    		NetPositionCommand npc=(NetPositionCommand)itr.next();
		    		if(npc.getSymbol().equals(netpos.getSymbol())){
		    		   		netpos.setPrice2(npc.getPrice2());
		    		}
		    	}
		        return netpos;
		    } 
		}
		
		 private final class BrokerProfitLossMapper1 implements RowMapper<NetPositionCommand> {

			    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
			    	NetPositionCommand netpos = new NetPositionCommand();
			     	netpos.setPrice2(rs.getString(1));
			      	netpos.setSymbol(rs.getString(2));
			        return netpos;
			    } 
		    }
		 /***************************************************/
		 
		 public List<NetPositionCommand> findExchangeProfitLoss() {
				String sql = "SELECT SUM(ql),price,symbol,ptype FROM ordermgmt where brokeId='' and user_id='' "
						+ "and type='SELL' group by symbol,ptype,price ";
				jdbcTemplate = new JdbcTemplate(dataSource);
				List<NetPositionCommand> profitloss=jdbcTemplate.query(sql,new ExchangeProfitLossMapper());
				return profitloss;
			}
			private final class ExchangeProfitLossMapper implements RowMapper<NetPositionCommand> {

			    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
			    	NetPositionCommand netpos = new NetPositionCommand();
			    	netpos.setLot1(rs.getString(1));
			    	netpos.setPrice1(rs.getString(2));
			    	netpos.setSymbol(rs.getString(3));
			    	netpos.setPtype(rs.getString(4));
			    	String sql1 ="SELECT price,symbol FROM ordermgmt  where type='BUY' and brokeId=''"+
			    			" and user_id='' and ptype='"+netpos.getPtype()+"' and symbol='"+netpos.getSymbol()+"' group by price";
			    	List netPositions=jdbcTemplate.query(sql1,new ExchangeProfitLossMapper1());
			    	Iterator itr=netPositions.iterator();
			    	while(itr.hasNext()){
			    		NetPositionCommand npc=(NetPositionCommand)itr.next();
			    		if(npc.getSymbol().equals(netpos.getSymbol())){
			    		   		netpos.setPrice2(npc.getPrice2());
			    		}
			    	}
			        return netpos;
			    } 
			}
			
			 private final class ExchangeProfitLossMapper1 implements RowMapper<NetPositionCommand> {
				    public NetPositionCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
				    	NetPositionCommand netpos = new NetPositionCommand();
				     	netpos.setPrice2(rs.getString(1));
				      	netpos.setSymbol(rs.getString(2));
				        return netpos;
				    } 
			    }
	
}